﻿Imports VN.INS.DL.DH
Imports VN.INS.BL.BE
Public Class UsuarioDA
  
    Public Function ListaUsuario(ByVal strUser As String, ByVal strPass As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable        

        Try
            objData.CreateInputOracle("v_Usuario", strUser, 30)
            objData.CreateInputOracle("v_Clave", strPass, 50)
            objData.CreateOutputOracle("p_Cursor", "", 100)
            objDt = objData.ExecuteOracle("SP_LOGIN", "Conexion.Oracle")          
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function ListaUsuario() As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String

        Try

            lxc_SQL = "SELECT USUARIO, PERFIL, USER_NAME, PASSWORD FROM USUARIO WHERE ESTADO = 'A' ORDER BY USUARIO "
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt

        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function ListaUsuarioperfil(ByVal id As String, ByVal strPersona As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "SELECT DISTINCT P.NOMBRES || ' ' || P.APELLIDO_PATERNO || ' ' || P.APELLIDO_MATERNO AS  NOMBRE,P.PERSONA " & _
                "FROM USUARIO U INNER JOIN PERSONA P ON P.USUARIO = U.USUARIO INNER JOIN POA O ON P.PERSONA IN (O.PERSONA_MONITOREO ,O.PERSONA_ESPECIALISTA) WHERE U.ESTADO = 'A' AND U.PERFIL = '" + id + "' "
            If strPersona <> "" Then
                lxc_SQL = lxc_SQL & " AND '" & strPersona & "' IN (P.JEFE_INMEDIATO,P.PERSONA,O.PERSONA_ESPECIALISTA) "
            End If
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt

        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function ListaMonitoreadorMantPoa(ByVal strCodPPER As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "SELECT DISTINCT P.NOMBRES || ' ' || P.APELLIDO_PATERNO || ' ' || P.APELLIDO_MATERNO AS  NOMBRE,P.PERSONA " & _
                      "FROM USUARIO U INNER JOIN PERSONA P ON P.USUARIO = U.USUARIO INNER JOIN POA O ON P.PERSONA IN (O.PERSONA_MONITOREO ,O.PERSONA_ESPECIALISTA) WHERE U.ESTADO = 'A' AND U.PERFIL = 'MO' and P.PPER= '" & strCodPPER & "' ORDER BY NOMBRE "
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt

        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function ListaEspecialistaMantPoa(ByVal strCodPPER As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "SELECT DISTINCT P.NOMBRES || ' ' || P.APELLIDO_PATERNO || ' ' || P.APELLIDO_MATERNO AS  NOMBRE,P.PERSONA " & _
                      "FROM USUARIO U INNER JOIN PERSONA P ON P.USUARIO = U.USUARIO INNER JOIN POA O ON P.PERSONA IN (O.PERSONA_MONITOREO ,O.PERSONA_ESPECIALISTA) WHERE U.ESTADO = 'A' AND U.PERFIL = 'EP' and P.PPER= '" & strCodPPER & "' ORDER BY NOMBRE "
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt

        Catch ex As Exception
            Throw ex
        End Try
    End Function

End Class

